

import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

import davidlauzon.activerecord.ActiveRecord;
import davidlauzon.activerecord.Constants;
import davidlauzon.activerecord.connection.ConnectionAdapter;
import davidlauzon.activerecord.connection.SqliteAdapter;
import davidlauzon.activerecord.manager.SelectManager;
import davidlauzon.activerecord.models.DB;
import davidlauzon.activerecord.models.Person;
import davidlauzon.activerecord.models.Product;
import davidlauzon.activerecord.nodes.AliasableColumnIF;
import davidlauzon.activerecord.nodes.SortableColumnIF;
import davidlauzon.activerecord.nodes.Table;
import davidlauzon.activerecord.nodes.field.Field;
import davidlauzon.activerecord.nodes.field.FloatField;
import davidlauzon.activerecord.nodes.field.IntegerField;
import davidlauzon.activerecord.nodes.field.StringField;
import davidlauzon.activerecord.recordset.RecordSet;

public class ActiveRecordTestWithoutJunit
{
    /*********************************************************************************************
     * VARIABLES
     *********************************************************************************************/
    private ConnectionAdapter _adapter;
    private SelectManager _selectManager;
    

    public static void main(String[] args) {
        new ActiveRecordTestWithoutJunit();
    }
    
    static class Paysan extends ActiveRecord
    {   
        static private Class __klass = Paysan.class;
        
        static public final Field ID   = new IntegerField("id", true);
        static public final Field NAME = new StringField("name", false);
        static public final Field SIZE = new FloatField("size", false);
        static public final Field AGE  = new IntegerField("age", false);

        static public final Field[] FIELDS = {ID, NAME, SIZE, AGE};
        static public final Table   TABLE  = new Table("paysans", FIELDS);

        // MUST BE INSTANCIATED AFTER ALL OTHER VARIABLES
        static protected final Paysan instance = new Paysan(); 
     

        public Paysan() {
        }
        
        static public Paysan  build( Map<Field, Object> attributes)                              { return (Paysan)build(__klass, attributes); }
        static public Paysan  create(Map<Field, Object> attributes) throws SQLException          { return (Paysan)create(__klass, attributes); }
        static public boolean update(long id, Map<Field, Object> attributes) throws SQLException { return update(__klass, id, attributes); }
        static public boolean delete(long id) throws SQLException                                { return delete(__klass, id); }
        
        @Override public Table        getTable()          { return TABLE; }
        @Override public Field[]      getFields()         { return FIELDS; }
        @Override public ActiveRecord getStaticInstance() { return instance; }
    }

    
    public ActiveRecordTestWithoutJunit() {
        setUp();
//        log("main", "Table for Payson : " + Paysan.TABLE.name());
//        log("main", "Table for Product : " + DB.PRODUCTS_TABLE.name());
//        log("main", "Table for People : " + DB.PEOPLE_TABLE.name());
//        log("main", "Table for People : " + Person.instance.getTable());
//        log("main", "Table for People : " + Person.TABLE.name());
        testActiveRecord();
    }
    
    
    
    /*********************************************************************************************
     * TEST METHODS
     *********************************************************************************************/
    protected void setUp() {
        try {
            _adapter = new SqliteAdapter("test.db");
            DB.getInstance().setConnection( _adapter );
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    
    /*********************************************************************************************
     * TEST METHODS
     *********************************************************************************************/
    public void testActiveRecord() {
        try {
//            printRecords();
            
            // Create a record
            Map<Field, Object> values = new HashMap<Field, Object>();
            values.put(Person.NAME, "Priya Rey");
            values.put(Person.AGE,  32);
            values.put(Person.SIZE, 42.2436f);
            
            Person person = Person.create(values);
            log("testActiveRecord", "Should have created (new=" + person.isNewRecord() + ") a Person: " + person.toString());
            printRecords();
        
            log("\n\ntestActiveRecordSelect", "Testing SELECT queries");
            

            String sql;
            RecordSet set;
            Field[] fields;
            
            // SELECT : where
//            _selectManager = Person.find("id > ?")._(0);
//            printRecordSet(_selectManager.toSql(), _selectManager.query());
            
            // SELECT : where =====> CAUSE PROBLEM : columns() is not used
//            fields = new Field[]{ Person.AGE };
//            _selectManager = Person.find("id > ?")._(1).distinct().columns(fields);
//            printRecordSet(_selectManager.toSql(), _selectManager.query());

            //fields = new Field[]{ Person.AGE };
            _selectManager = Person.find("").distinct().
                group(Person.AGE).order(Person.ID);
            
            log("SQL : ", _selectManager.toSql());
            set = _selectManager.query();
            
            //RecordSet set = Person.find("AGE BETWEEN ? AND ?")._(2)._(99).group(Person.AGE.as("a1")).order(Person.ID).query();
            
            
            
            Person record;
            
            Person.update(34, null);
            
            while (set.next()) {
                record = (Person) set.fetchRecord();
                log("Record:", record );
                log("Record:", record.get(Person.NAME) );
            }
//            printRecordSet(_selectManager.toSql(), _selectManager.query());
            
            // ALL query
//            fields = new Field[]{ Person.AGE };
//            _selectManager = Person.find("id > ?")._(1).distinct().columns(fields).
//                group(Person.AGE).order(Person.ID).limit(100).offset(1);
//            printRecordSet(_selectManager.toSql(), _selectManager.query());
            
            // No condition
//            fields = new Field[]{ Person.AGE };
//            _selectManager = Person.find("").distinct().columns(fields).
//                group(Person.AGE).order(Person.ID).limit(100).offset(1);
//            printRecordSet(_selectManager.toSql(), _selectManager.query());
            
        } catch (SQLException e) {
            e.printStackTrace();
        } 
    }
    
    private void printRecordSet( String sql, RecordSet set ) throws SQLException {
        log("sql:", sql);
        
        long   id;
        String name;
        float  size;
        int    age;
        
        while (set.next()) {
            id    = set.getLong(0);
            name  = set.getString(1);
            size  = set.getFloat(2);
            age   = set.getInt(3);
            System.out.println("\t=> #" + id + " " + name + ": " + size + " ; " + age);
        }
    }
    
    
    /*********************************************************************************************
     * INTERNAL METHODS
     *********************************************************************************************/

    protected void log(String funcName, Object message) {
        System.out.println(getClass().getSimpleName() + "::" + funcName + "() " + message);
    }
    

    protected int printRecords() throws SQLException {
        RecordSet set;
        int size = 0;
        
        long   l;
        String s;
        float  f;
        int    i;
        
        set = getAdapter().executeQuery( Constants.SELECT_ALL_PEOPLE);
        
        while ( set.next() ) {
            l = set.getLong(0);
            s = set.getString(1);
            f = set.getFloat(2);
            i = set.getInt(3);
            size++;
            log("listing db records", "\t=> #" + l + " " + s + ": " + f + " ; " + i);
        }
        set.close();
        
        return size;
    }

    protected ConnectionAdapter getAdapter()
    {
        return _adapter;
    }

}
